package solutions.ferpa.ztest.intf;

import br.com.telescope.adsrt.dom.DomFlag;
import br.com.telescope.adsrt.dom.DomInteger;
import br.com.telescope.adsrt.dom.DomOpcao;
import br.com.telescope.adsrt.dom.DomSourceCode;
import br.com.telescope.adsrt.lib.*;
import br.com.telescope.sql.DataBaseSchemaManager;
import br.com.telescope.t2.ServiceModel;
import br.com.telescope.t2.exception.ApplicationRuntimeException;
import br.com.telescope.t2.exception.UnexpectedException;
import br.com.telescope.t2.feature.CustomFeature;
import br.com.telescope.t2.feature.MethodFeature;
import br.com.telescope.util.JdbcUtil;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import solutions.ferpa.ztest.ent.*;

/**
 * Verificação da estrutura do BD.
 * <p>
 * Telas para criar/atualizar todos os objetos de banco relacionados com 
 * a aplicação (tabelas, sequences, etc.)
 * <P>
 * Telescope origin: RTDBUT.UTL_DB_SCHEMA
 * <P>
 * @author TELESCOPE - Build task T2 Web Interface Generator
 * <P>
 * @version 0.1
 */
public class UtlDbSchema extends ServiceModel {
    
    // <editor-fold defaultstate="collapsed" desc="Generated Code">
    public static final String GLOBAL_NAME = "RTDBUT.UTL_DB_SCHEMA";
    
    @Override
    public String globalName() {
        return GLOBAL_NAME;
    }
    
    {
        setTitle("Verificação da estrutura do BD");
    }
    
    
    // Record items
    private DomOpcao aSchema;
    private DomFlag aDropColumns;
    private DomFlag aRecreateViews;
    private DomFlag aDropPkCascade;
    private DomSourceCode aCommand;
    private DomInteger aRegistros;
    private DomFlag aReplicar;
    
    // Query items
    
    @Override
    public void initAttributes() {
        
        aSchema = new DomOpcao();
        aSchema.context(ctx).name("SCHEMA").label("Schema").unbound(true).group(true);
        
        aDropColumns = new DomFlag();
        aDropColumns.context(ctx).name("DROP_COLUMNS").label("Drop columns").unbound(true);
        aDropColumns.setDefaultValue("N");
        
        aRecreateViews = new DomFlag();
        aRecreateViews.context(ctx).name("RECREATE_VIEWS").label("Recreate VIEWS").unbound(true);
        aRecreateViews.setDefaultValue("N");
        
        aDropPkCascade = new DomFlag();
        aDropPkCascade.context(ctx).name("DROP_PK_CASCADE").label("Use cascade on drop PK").unbound(true);
        aDropPkCascade.setDefaultValue("S");
        
        aCommand = new DomSourceCode();
        aCommand.context(ctx).name("COMMAND").label("DDL Script").unbound(true);
        
        aRegistros = new DomInteger();
        aRegistros.context(ctx).name("REGISTROS").label("Registros").unbound(true);
        
        aReplicar = new DomFlag();
        aReplicar.context(ctx).name("REPLICAR").label("Replicar").unbound(true);
        
    }
    
    @Override
    public String getDefaultOrderBy() {
        return "";
    }
    // </editor-fold>
    
    // <editor-fold defaultstate="collapsed" desc="HOME">
    public class FHome extends CustomFeature {
        
        @Override
        public void initForm() {
            aSchema.setOptional(false);
            addField(aSchema, "rqd");
            addField(aDropColumns, "opt");
            addField(aRecreateViews, "opt");
            addField(aDropPkCascade, "opt");
        }
        
        @Override
        public void preDisplay() {
            try {
                popularSchemas();
            } catch (AdsException ex) {
                throw new ApplicationRuntimeException(ex);
            }
        }
        
        @Override
        public void onAction() {
            addSubmit(getFeature("SCHEMA_DIAG")).label("Diagnosticar schema").icon("gerar.gif");
            addSubmit(getFeature("SCHEMA_INFO")).label("Informações de um schema").icon("procedimento.gif");
        }
        
        {
            setFeatureName("HOME");
            setTitle("Opções de execução");
            setPrimary(false);
        }
    }
    // </editor-fold>
    
    // <editor-fold defaultstate="collapsed" desc="SCHEMA_DIAG">
    public class FSchemaDiag extends CustomFeature {
        
        @Override
        public void initForm() {
            addField(aCommand, "opt");
            aRegistros.setHidden(true);
            aRegistros.setDefaultValue("100");
            addField(aRegistros, "hidden");
            aReplicar.setDefaultValue("S");
            addField(aReplicar, "opt");
        }
        
        @Override
        public void preDisplay() {
            try {
                updateSchema();
            } catch (AdsException ex) {
                throw new ApplicationRuntimeException(ex);
            }
        }
        
        @Override
        public void onAction() {
            addSubmit(getFeature("HOME")).label("Voltar").icon("seta_esquerda.gif");
            addSubmit(getFeature("EXECUTAR_COMANDOS")).label("Executar SQL").shortCutKey("F3").icon("gerar.gif");
        }
        
        {
            setFeatureName("SCHEMA_DIAG");
            setTitle("Diagnosticar schema");
            setPrimary(false);
        }
    }
    // </editor-fold>
    
    // <editor-fold defaultstate="collapsed" desc="SCHEMA_INFO">
    public class FSchemaInfo extends CustomFeature {
        
        @Override
        public void initForm() {
        }
        
        @Override
        public void preDisplay() {
            try {
                printSchemaInfo();
            } catch (AdsException ex) {
                throw new ApplicationRuntimeException(ex);
            }
        }
        
        @Override
        public void onAction() {
            addSubmit(getFeature("HOME")).label("Opções de execução").icon("seta_esquerda.gif");
        }
        
        {
            setFeatureName("SCHEMA_INFO");
            setTitle("Informações de um schema");
            setPrimary(false);
        }
    }
    // </editor-fold>
    
    // <editor-fold defaultstate="collapsed" desc="TABLE_INFO">
    public class FTableInfo extends CustomFeature {
        
        @Override
        public void initForm() {
        }
        
        @Override
        public void preDisplay() {
            try {
                printTableInfo();
            } catch (AdsException ex) {
                throw new ApplicationRuntimeException(ex);
            }
        }
        
        {
            setFeatureName("TABLE_INFO");
            setTitle("Informações osbre uma tabela");
            setPrimary(false);
        }
    }
    // </editor-fold>
    
    // <editor-fold defaultstate="collapsed" desc="EXECUTAR_COMANDOS">
    public class FExecutarComandos extends MethodFeature {
        
        @Override
        public void run() {
            preRun();
            try {
                executarComandos();
            } catch (AdsException ex) {
                throw new ApplicationRuntimeException(ex);
            }
        }
        
        {
            setFeatureName("EXECUTAR_COMANDOS");
            setTitle("Executar SQL");
            setShortCutKey("F3");
            setPrimary(false);
        }
    }
    // </editor-fold>
    
    // <editor-fold defaultstate="collapsed" desc="initFeatures">
    
    @Override
    protected void initFeatures() {
        addFeature(new FHome());
        addFeature(new FSchemaDiag());
        addFeature(new FSchemaInfo());
        addFeature(new FTableInfo());
        addFeature(new FExecutarComandos());
        super.initFeatures();
    }
    
    @Override
    public String getDefaultFeature() {
        return "HOME";
    }
    // </editor-fold>
    
    // <editor-fold defaultstate="collapsed" desc="Getters and Setters">
    
    public String getSchema() {
        return aSchema.getValue();
    }
    
    public void setSchema(String value) {
        this.aSchema.setValue(value);
    }
    
    public String getDropColumns() {
        return aDropColumns.getValue();
    }
    
    public void setDropColumns(String value) {
        this.aDropColumns.setValue(value);
    }
    
    public String getRecreateViews() {
        return aRecreateViews.getValue();
    }
    
    public void setRecreateViews(String value) {
        this.aRecreateViews.setValue(value);
    }
    
    public String getDropPkCascade() {
        return aDropPkCascade.getValue();
    }
    
    public void setDropPkCascade(String value) {
        this.aDropPkCascade.setValue(value);
    }
    
    public String getCommand() {
        return aCommand.getValue();
    }
    
    public void setCommand(String value) {
        this.aCommand.setValue(value);
    }
    
    public Integer getRegistros() {
        return aRegistros.getValue();
    }
    
    public void setRegistros(Integer value) {
        this.aRegistros.setValue(value);
    }
    
    public String getReplicar() {
        return aReplicar.getValue();
    }
    
    public void setReplicar(String value) {
        this.aReplicar.setValue(value);
    }
    // </editor-fold>
    
    /**
     * Executar SQL.
     * <p>
     * Mover e executar o script na tela do UTL_DB_SQL.
     * <P>
     */
    private void executarComandos() throws AdsException {
        // <user-code id="777372" parentId="576122" hashcode="-2b6954b5" length="281">
        // import br.com.telescope.util.JdbcUtil;
        String script = ctx.getParameter("A_COMMAND").trim();
        ctx.readFields(aReplicar);
        DataBaseSchemaManager mgr = new DataBaseSchemaManager(ctx);
        mgr.executeScript(script, aReplicar.isTrue());
        //getFeature("SCHEMA_DIAG").run();
        doIndex();
        // </user-code>
    }
    
    /**
     * Popular schemas.
     * <p>
     * Pesquisar quais os schemas disponíveis. Manter, como sugestão, o nome 
     * do usuário na conexão com o banco ou o último utilizado.
     * <P>
     */
    private void popularSchemas() throws AdsException {
        // <user-code id="806837" parentId="207388" hashcode="32ad3673" length="599">
        // import java.sql.DatabaseMetaData;
        // import br.com.telescope.t2.exception.UnexpectedException;
        aSchema.clearOptions();
        Connection conn = ctx.getConnection();
        ResultSet rs = null;
        DatabaseMetaData meta = null;
        try {
            meta = conn.getMetaData();
            rs = meta.getSchemas();
            while (rs.next()) {
                String schema = rs.getString("TABLE_SCHEM");
                aSchema.addOption(schema,schema);
            }
        } catch (SQLException ex) {
            throw new UnexpectedException(ex);
        } finally {
            JdbcUtil.close(rs);
        }
        aSchema.setValue(ctx.getPreference("CONFIG.DB.DEFAULT.SCHEMA", "public"));
        // </user-code>
    }
    
    /**
     * Montar informações sobre o schema.
     * <p>
     * Monta uma lista com todos as tabelas do banco.
     * <P>
     * O nome da tabela é um link para a tela de debgug da tabela.
     * <P>
     */
    private void printSchemaInfo() throws AdsException {
        // <user-code id="806838" parentId="207406" hashcode="5dfc9664" length="833">
        String schema = ctx.getParameter("A_SCHEMA");
        ctx.setPreference("CONFIG.DB.DEFAULT.SCHEMA", schema);
        
        // Busca a conexão com o banco de dados
        Connection conn = ctx.getConnection();
        
        // Inicia o acesso aos metadados do BD e descobre o nome do catálogo
        try {
            DatabaseMetaData meta = conn.getMetaData();
            String catalog = conn.getCatalog();
            String[] types = {"TABLE"};
            ctx.p("Tabelas:<ul>");
            ResultSet rs = meta.getTables(catalog, schema, "%", types);
            while (rs.next()) {
                String tableName = rs.getString("TABLE_NAME");
                ctx.p("<li><a href=?Z_ACTION=TABLE_INFO&TABLE_NAME=" + tableName +
                        "&SCHEMA=" + schema + ">" + tableName.toUpperCase() + "</a>");
            }
            rs.close();
            ctx.p("</ul>");
        } catch (SQLException ex) {
            throw new UnexpectedException(ex);
        }
        // </user-code>
    }
    
    /**
     * Montar informações da tabela.
     * <p>
     * Mostra todas as informações de uma tabela específica.
     * <P>
     */
    private void printTableInfo() throws AdsException {
        // <user-code id="806839" parentId="207408" hashcode="-27b733c0" length="1106">
        String tableName = ads.getParameter("TABLE_NAME");
        String schema = ads.getParameter("SCHEMA");
        
        // Busca a conexão com o banco de dados
        Connection conn = ads.getConnection();
        
        // Inicia o acesso aos metadados do BD e descobre o nome do catálogo
        DatabaseMetaData meta = null;
        String catalog;
        try {
            meta = conn.getMetaData();
            catalog = conn.getCatalog();
            ads.p("<h1>" + tableName + "</h1>");
            ads.p("<h3>Columns</h3>");
            ads.p(AdsUtil.htmlTable(meta.getColumns(catalog, schema, tableName ,"%")));
            ads.p("<h3>PrimaryKeys</h3>");
            ads.p(AdsUtil.htmlTable(meta.getPrimaryKeys(catalog, schema, tableName)));
            ads.p("<h3>IndexInfo</h3>");
            ads.p(AdsUtil.htmlTable(meta.getIndexInfo(catalog, schema, tableName, false,false)));
            ads.p("<h3>ImportedKeys</h3>");
            ads.p(AdsUtil.htmlTable(meta.getImportedKeys(catalog, schema, tableName)));
            ads.p("<h3>ExportedKeys</h3>");
            ads.p(AdsUtil.htmlTable(meta.getExportedKeys(catalog, schema, tableName)));
        } catch (SQLException ex) {
            throw new AdsException(ex,"",AdsException.UNKNOWN,ex.toString());
        }
        // </user-code>
    }
    
    /**
     * Sugerir alterações do schema.
     * <p>
     * Procedimento que analisa a conexão com o banco de dados e monta um 
     * script para ajusta-lo de acordo com as informações existentes nas 
     * packages da aplicação.
     */
    private void updateSchema() throws AdsException {
        // <user-code id="777415" parentId="207024" hashcode="-321e3729" length="920">
        // import br.com.telescope.sql.DataBaseSchemaManager;
        String prefname = "CONFIG.DB.DEFAULT.SCHEMA";
        String schema = ctx.getParameter("A_SCHEMA");
        if (schema.isEmpty()) {
            schema = ctx.getPreference(prefname);
        } else {
            try {
                ctx.setPreference(prefname, schema);
            } catch (Exception ignore) { }
        }
        boolean dropCascade = ctx.getParameter("A_DROP_PK_CASCADE", "N").equals("S");
        boolean recreateViews = ctx.getParameter("A_RECREATE_VIEWS", "N").equals("S");
        boolean dropColumn = ctx.getParameter("A_DROP_COLUMNS", "N").equals("S");
        DataBaseSchemaManager dbsm = new DataBaseSchemaManager(ctx);
        String out = dbsm.getUpdateSchemaDDL(schema, dropCascade, recreateViews, dropColumn);
        
        if (out.length() == 0) {
            ctx.showSuccess("Este schema não precisa de ajustes!");
        } else {
            aCommand.setValue(out.replaceAll("\\n",";\n"));
            aRegistros.setValue(100);
            aReplicar.setValue("S");
        }
        // </user-code>
    }
}
